import { pgTable, text, varchar, timestamp, boolean, jsonb, uuid, pgEnum, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// 用户资料表 - 用于存储用户的个人资料信息
export const userProfiles = pgTable('user_profiles', {
  id: uuid('id').primaryKey().notNull(), // 与 users 表的 id 字段关联
  username: varchar('username', { length: 100 }).notNull().unique(),
  email: varchar('email', { length: 255 }).unique(),
  avatarUrl: text('avatar_url'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull()
});

// 定义成员角色枚举类型
export const teamMemberRoleEnum = pgEnum('team_member_role', ['Owner', 'Member']);

// 定义成员状态枚举类型
export const teamMemberStatusEnum = pgEnum('team_member_status', ['active', 'invited', 'inactive']);


// 团队表 - 用于存储团队信息
export const teams = pgTable('teams', {
  teamId: uuid('team_id').primaryKey().defaultRandom().notNull(),
  name: varchar('name', { length: 100 }).notNull(),
  ownerId: uuid('owner_id').notNull().references(() => userProfiles.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 }),
  isDeleted: boolean('is_deleted').default(false).notNull()
});

// 团队成员表 - 用于存储团队成员关系
export const teamMembers = pgTable('team_members', {
  id: uuid('id').primaryKey().defaultRandom().notNull(),
  teamId: uuid('team_id').notNull().references(() => teams.teamId),
  userId: uuid('user_id').notNull().references(() => userProfiles.id).unique(),
  role: teamMemberRoleEnum('role').notNull(),
  status: teamMemberStatusEnum('status').default('active').notNull(),
  email: varchar('email', { length: 255 }),
  invitedBy: uuid('invited_by').references(() => userProfiles.id),
  inviter: varchar('inviter', { length: 100 }),
  invitedAt: timestamp('invited_at'),
  joinedAt: timestamp('joined_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 })
});

// 定义表关系
export const teamsRelations = relations(teams, ({ one, many }) => ({
  owner: one(userProfiles, {
    fields: [teams.ownerId],
    references: [userProfiles.id],
  }),
  members: many(teamMembers)
}));

export const teamMembersRelations = relations(teamMembers, ({ one }) => ({
  team: one(teams, {
    fields: [teamMembers.teamId],
    references: [teams.teamId],
  }),
  user: one(userProfiles, {
    fields: [teamMembers.userId],
    references: [userProfiles.id],
  })
}));

export const userProfilesRelations = relations(userProfiles, ({ one }) => ({
  ownedTeam: one(teams, {
    fields: [userProfiles.id],
    references: [teams.ownerId],
  }),
  teamMembership: one(teamMembers, {
    fields: [userProfiles.id],
    references: [teamMembers.userId],
  })
}));

// 定义项目状态枚举类型
export const projectStatusEnum = pgEnum('project_status', ['ACTIVE', 'DELETE']);
export const searchObjectiveEnum = pgEnum('search_objective', ['posts', 'creator']);

// 项目表 - 用于存储项目的基本信息，包括项目标题、内容、所属团队和用户
export const projects = pgTable('projects', {
  projectId: uuid('project_id').primaryKey().defaultRandom().notNull(),
  teamId: uuid('team_id').notNull().references(() => teams.teamId),
  userId: uuid('user_id').notNull().references(() => userProfiles.id),
  name: varchar('name', { length: 255 }).notNull(),
  content: text('content').notNull(),
  searchKeywords: text('search_keywords').array(),
  matchingRules: text('matching_rules').array(),
  searchConditions: jsonb('search_conditions'),
  searchObjective: searchObjectiveEnum('search_objective').notNull(),
  remark: varchar('remark', { length: 255 }),
  status: projectStatusEnum('status').default('ACTIVE').notNull(),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull()
});

// 搜索关键词表 - 用于存储搜索关键词信息
export const keywords = pgTable('keywords', {
  keywordId: uuid('keyword_id').primaryKey().defaultRandom().notNull(),
  projectId: uuid('project_id').notNull().references(() => projects.projectId),
  keyword: varchar('keyword', { length: 255 }).notNull(),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull()
});

// 定义关键词关系
export const keywordsRelations = relations(keywords, ({ one }) => ({
  project: one(projects, {
    fields: [keywords.projectId],
    references: [projects.projectId],
  })
}));

// 创作者表 - 用于存储创作者信息
export const creators = pgTable('creators', {
  creatorId: uuid('creator_id').primaryKey().defaultRandom().notNull(),
  platform: varchar('platform', { length: 50 }).notNull(),
  username: varchar('username', { length: 255 }).notNull(),
  bio: text('bio'),
  followerCount: integer('follower_count').default(0).notNull(),
  fetchedAt: timestamp('fetched_at').defaultNow().notNull()
});

// 文章表 - 用于存储文章信息
export const posts = pgTable('posts', {
  postId: uuid('post_id').primaryKey().defaultRandom().notNull(),
  platform: varchar('platform', { length: 50 }).notNull(),
  creatorId: uuid('creator_id').notNull().references(() => creators.creatorId),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content').notNull(),
  postTime: timestamp('post_time').notNull(),
  fetchedAt: timestamp('fetched_at').defaultNow().notNull(),
  likeCount: integer('like_count').default(0).notNull(),
});

// 定义文章关系
export const postsRelations = relations(posts, ({ one }) => ({
  creator: one(creators, {
    fields: [posts.creatorId],
    references: [creators.creatorId],
  })
}));

// 定义结果表状态枚举类型
export const resultStatusEnum = pgEnum('result_status', ['APPROVED', 'REJECTED', 'PENDING', 'SKIPPED']);

// 结果表 - 用于存储搜索结果
export const results = pgTable('results', {
  resultId: uuid('result_id').primaryKey().defaultRandom().notNull(),
  projectId: uuid('project_id').notNull().references(() => projects.projectId),
  creatorId: uuid('creator_id').notNull().references(() => creators.creatorId),
  postId: uuid('post_id').notNull().references(() => posts.postId),
  searchResult: jsonb('search_result'),
  matchScore: jsonb('match_score'),
  userVerdict: resultStatusEnum('user_verdict'),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 }),
  isDeleted: boolean('is_deleted').default(false).notNull()
});

// 定义结果表关系
export const resultsRelations = relations(results, ({ one }) => ({
  project: one(projects, {
    fields: [results.projectId],
    references: [projects.projectId],
  }),
  creator: one(creators, {
    fields: [results.creatorId],
    references: [creators.creatorId],
  }),
  post: one(posts, {
    fields: [results.postId],
    references: [posts.postId],
  })
}));

// 定义项目关系
export const projectsRelations = relations(projects, ({ one }) => ({
  team: one(teams, {
    fields: [projects.teamId],
    references: [teams.teamId],
  }),
  user: one(userProfiles, {
    fields: [projects.userId],
    references: [userProfiles.id],
  })
}));

// 更新团队关系，添加项目关联
export const teamsRelationsWithProjects = relations(teams, ({ one, many }) => ({
  owner: one(userProfiles, {
    fields: [teams.ownerId],
    references: [userProfiles.id],
  }),
  members: many(teamMembers),
  projects: many(projects)
}));

// 更新用户关系，添加项目关联
export const userProfilesRelationsWithProjects = relations(userProfiles, ({ one, many }) => ({
  ownedTeam: one(teams, {
    fields: [userProfiles.id],
    references: [teams.ownerId],
  }),
  teamMembership: one(teamMembers, {
    fields: [userProfiles.id],
    references: [teamMembers.userId],
  }),
  projects: many(projects)
}));

// 定义任务状态枚举类型
export const taskStatusEnum = pgEnum('task_status', ['PENDING', 'PROCESSING', 'COMPLETED', 'ERROR']);

// 任务表 - 用于存储任务的基本信息，包括任务类型、状态、参数等
export const tasks = pgTable('tasks', {
  taskId: uuid('task_id').primaryKey().defaultRandom().notNull(),
  projectId: uuid('project_id').notNull().references(() => projects.projectId),
  taskType: varchar('task_type', { length: 50 }).notNull(),
  inputData: jsonb('input_data').notNull(),
  outputData: jsonb('output_data'),
  errorLog: jsonb('error_log'),
  status: taskStatusEnum('status').default('PENDING').notNull(),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 })
});

// 定义任务关系
export const tasksRelations = relations(tasks, ({ one }) => ({
  project: one(projects, {
    fields: [tasks.projectId],
    references: [projects.projectId],
  })
}));

// 更新项目关系，添加任务关联
export const projectsRelationsWithTasks = relations(projects, ({ one, many }) => ({
  team: one(teams, {
    fields: [projects.teamId],
    references: [teams.teamId],
  }),
  user: one(userProfiles, {
    fields: [projects.userId],
    references: [userProfiles.id],
  }),
  tasks: many(tasks)
}));

// 任务类型表（TaskTypes）- 用于存储任务类型的基本信息、参数校验规则和任务工作流配置
export const taskTypes = pgTable('task_types', {
  taskTypeId: uuid('task_type_id').primaryKey().defaultRandom().notNull(),
  taskType: varchar('task_type', { length: 50 }).notNull(),
  validationRules: jsonb('validation_rules').notNull(),
  workflowConfig: jsonb('workflow_config').notNull(),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 }),
  isDeleted: boolean('is_deleted').default(false).notNull()
});

// API配置表（APIConfig）- 用于存储不同任务类型可能需要调用的不同平台API的配置信息
export const apiConfig = pgTable('api_config', {
  configId: uuid('config_id').primaryKey().defaultRandom().notNull(),
  taskType: uuid('task_type').notNull().references(() => taskTypes.taskTypeId),
  apiKey: text('api_key').notNull(),
  baseUrl: text('base_url').notNull(),
  apiRoute: text('api_route').notNull(),
  rpmLimit: integer('rpm_limit').notNull(),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 }),
  isDeleted: boolean('is_deleted').default(false).notNull()
});

// 定义API配置与任务类型的关系
export const apiConfigRelations = relations(apiConfig, ({ one }) => ({
  taskTypeRelation: one(taskTypes, {
    fields: [apiConfig.taskType],
    references: [taskTypes.taskTypeId],
  })
}));

// 定义任务与任务类型的关系
export const tasksWithTypeRelations = relations(tasks, ({ one }) => ({
  project: one(projects, {
    fields: [tasks.projectId],
    references: [projects.projectId],
  }),
  taskTypeRelation: one(taskTypes, {
    fields: [tasks.taskType],
    references: [taskTypes.taskType],
    relationName: 'taskTypeNameRelation'
  })
}));

// 解析规则表（ParsingRules）- 用于存储不同平台的解析规则，从API返回的数据中提取所需的信息
export const parsingRules = pgTable('parsing_rules', {
  ruleId: uuid('rule_id').primaryKey().defaultRandom().notNull(),
  platform: varchar('platform', { length: 50 }).notNull(),
  apiRoute: varchar('api_route', { length: 200 }).notNull(),
  parsingRule: jsonb('parsing_rule').notNull(),
  createTime: timestamp('create_time').defaultNow().notNull(),
  updateTime: timestamp('update_time').defaultNow().notNull(),
  remark: varchar('remark', { length: 255 }),
  isDeleted: boolean('is_deleted').default(false).notNull()
});

// 定义解析规则与API配置的关系
export const parsingRulesRelations = relations(parsingRules, ({ many }) => ({
  apiConfigs: many(apiConfig)
}));

// 更新API配置关系，添加解析规则关联
export const apiConfigWithRulesRelations = relations(apiConfig, ({ one }) => ({
  taskTypeRelation: one(taskTypes, {
    fields: [apiConfig.taskType],
    references: [taskTypes.taskTypeId],
  }),
  parsingRule: one(parsingRules, {
    fields: [apiConfig.apiRoute],
    references: [parsingRules.apiRoute],
  })
}));

// 评论表（Comments）- 用于存储文章下的评论
export const comments = pgTable('comments', {
  commentId: uuid('comment_id').primaryKey().defaultRandom().notNull(),
  platform: varchar('platform', { length: 50 }).notNull(),
  postId: uuid('post_id').notNull().references(() => posts.postId),
  creatorId: uuid('creator_id').notNull().references(() => creators.creatorId),
  content: text('content').notNull(),
  likeCount: integer('like_count'),
  commentTime: timestamp('comment_time').notNull(),
  fetchedAt: timestamp('fetched_at').defaultNow().notNull()
});

// 定义评论与文章、创作者的关系
export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.postId],
  }),
  creator: one(creators, {
    fields: [comments.creatorId],
    references: [creators.creatorId],
  })
}));

// 更新文章关系，添加评论关联
export const postsWithCommentsRelations = relations(posts, ({ one, many }) => ({
  creator: one(creators, {
    fields: [posts.creatorId],
    references: [creators.creatorId],
  }),
  comments: many(comments)
}));

// 线索类型枚举 - 定义线索的类型（文章、评论、创作者）
export const leadTypeEnum = pgEnum('lead_type', ['post', 'comment', 'creator']);

// 线索表（Leads）- 用于存储从搜索结果中挖掘出的线索
export const leads = pgTable('leads', {
  leadId: uuid('lead_id').primaryKey().defaultRandom().notNull(),
  userId: uuid('user_id').notNull().references(() => userProfiles.id),
  projectId: uuid('project_id').notNull().references(() => projects.projectId),
  resultId: uuid('result_id').notNull().references(() => results.resultId),
  type: leadTypeEnum('type').notNull(),
  targetId: uuid('target_id').notNull(),
  leadScore: integer('lead_score'),
  reason: text('reason'),
  isValid: boolean('is_valid').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull()
});

// 定义线索与用户、项目的关系
export const leadsRelations = relations(leads, ({ one }) => ({
  user: one(userProfiles, {
    fields: [leads.userId],
    references: [userProfiles.id],
  }),
  project: one(projects, {
    fields: [leads.projectId],
    references: [projects.projectId],
  })
}));

// 更新项目关系，添加线索关联
export const projectsWithLeadsRelations = relations(projects, ({ one, many }) => ({
  team: one(teams, {
    fields: [projects.teamId],
    references: [teams.teamId],
  }),
  user: one(userProfiles, {
    fields: [projects.userId],
    references: [userProfiles.id],
  }),
  leads: many(leads)
}));